/***********************************************
 * A class to create the required databases and 
 * tables.
 * @author Brian Renzenbrink
 ***********************************************/

package database;

import java.io.BufferedReader;
import java.io.DataInputStream;
import java.io.InputStreamReader;
import java.sql.*;

public class DB_creation{
	
	//-------------------------Static Global Strings------------------------------
	private static String url = "jdbc:mysql://localhost:3306/";
	private static String driver = "com.mysql.jdbc.Driver";
	private static String database = "testWorkouts";
	private static String userName = "athlete";
	private static String password = "drowssap";
	
	//---------------------------------Methods-------------------------------------
	/****************************************************************
	 * The initialization method that handles the creation of 
	 * all necessary databases and tables.  This is the only method
	 * accessible to other classes.
	 * @param uName: The username the mySQL administrator
	 * @param pWord: The password the mySQL administrator
	 ****************************************************************/
	public static void initialize(String uName, String pWord){
		
		//Check if the DB exists
		if(!DB_exists()){
			//If it does not, create it
			createDB(uName, pWord);
		}
		
		//Check if the tables already exist
		boolean tablesPresent = false;
		if( tableExists("weightlifting")){
			tablesPresent = true;
		}
		if( tableExists("endurance")){
			tablesPresent = true;
		}
		if( tableExists("exercises")){
			tablesPresent = true;
		}
		
		//If the tables already exists, ask the user if they wish to overwrite them
		String response = "y"; 
		if(tablesPresent){
			 System.out.println("Would you like to overwrite these tables?  y/n?"); 			    
			 try{
				 DataInputStream in = new DataInputStream(System.in);
				 BufferedReader d = new BufferedReader(new InputStreamReader(in));
				 response = d.readLine();
				 System.out.println(response);
			 }
			 catch (Exception e) {
				 e.printStackTrace();
			 }
		}
		
		//If so, do the following
		if(response.contains("y")){
			//Create the necessary tables
			createWorkoutTables();
			
			//Populate the exercises table
			populateExercises();
		}

		System.out.println("Finished initialization successfully");
	}//end initialize
	
	/****************************************************************
	 * A method to check if the required database is already created.
	 * @return true if the database exists, false otherwise
	 ****************************************************************/
	private static boolean DB_exists(){
		try{
			//Register the JDBC driver for MySQL.
		    Class.forName(driver);
		     
			//Define URL of database server on the localhost with default port number 3306.
			String currentUrl = url + database;
			
			//Get a connection to the database
			@SuppressWarnings("unused")
			Connection con = DriverManager.getConnection(currentUrl, userName, password);
		}
		catch (Exception e) {
			//e.printStackTrace();
			System.out.println("The database does not exist.  We can go ahead and create it.");
			return false;
		}//end catch
		System.out.println("The database already exists.  We do not need to create it.");
		return true;
	}//end DB_exists
	
	/****************************************************************
	 * A method to determine if a given table exists in the database.
	 * @param tableName:The table to check for
	 * @return True if the table exists, false otherwise
	 ****************************************************************/
	private static boolean tableExists(String tableName){
		System.out.println("Checking if table " + tableName + " exists.");
		try{
			Statement stmt;
			
		    //Register the JDBC driver for MySQL.
		    Class.forName(driver);
		     
		    //Define URL of database server on the localhost with default port number 3306.
			String currentUrl = url + database;
			
			//Get a connection to the database 
			Connection con = DriverManager.getConnection(currentUrl, userName, password);
			
			//Get a Statement object
			stmt = con.createStatement();
			
			stmt.executeQuery("SELECT * " + "from " + tableName);
			
			//Close the connection
			con.close();
		
			System.out.println("Table already exists.");
			return true;
		}
		catch (Exception e) {
			//e.printStackTrace();
			System.out.println("Table does not exist.");
			return false;
		}//end catch
	}//end tableExists
	
	/****************************************************************
	 * A method to create a database called 'testWorkouts' and 
	 * a user named 'athlete'.  
	 * @param uName: The default administrator username
	 * @param pWord: The default administrator password
	 ****************************************************************/
	private static void createDB(String uName, String pWord){
		 System.out.println("Creating User and Database");
		 try {
		     Statement stmt;
		     
		     //Register the JDBC driver for MySQL.
		     Class.forName(driver);
	
		     //Define URL of database server for
		     // database named mysql on the localhost
		     // with the default port number 3306.
		     String currentUrl = url + "mysql";
	
		     //Get a connection to the database 
		     //The user should be the default administrator
		     Connection con =DriverManager.getConnection(currentUrl, uName, pWord);
		    
		     //Get a Statement object
		     stmt = con.createStatement();
	
		     //Create the new database if it doesn't exist already;
		     stmt.executeUpdate("CREATE DATABASE IF NOT EXISTS " + database);
		      
		     //Register a new user on the database and enable all privileges.
		     stmt.executeUpdate("GRANT ALL ON " + database + ".* TO '" + userName + "'@'localhost' IDENTIFIED BY '" + password + "';");
		     
		     //Close the connection
		     con.close();
		 }catch( Exception e ) {
			 e.printStackTrace();
		 }//end catch
	}//end createDB
	
	/**********************************************************************
	 * A method to create a table with a given name and fields.
	 * @param tableName: The name of the table to create
	 * @param fields: An ordered array of the fields in the table
	 * @param dataTypes: An array of the associated data types in the table
	 * Notes: fields and dataTypes are required to be of the same length
	 *        If there is a pre-existing table with the same name, that 
	 *        original table will be dropped.
	 **********************************************************************/
	private static void createTable(String tableName, String fields[], String dataTypes[]){
		System.out.println("Creating a table");
		try {
			Statement stmt;
		     
		    //Register the JDBC driver for MySQL.
		    Class.forName(driver);
		     
		    //Define URL of database server on the localhost with default port number 3306.
			String currentUrl = url + database;
			
			//Get a connection to the database 
			Connection con = DriverManager.getConnection(currentUrl, userName, password);
			
			//Get a Statement object
			stmt = con.createStatement();
			
			//As a precaution, delete myTable if it already exists as residue from a
			//previous run.  Otherwise, if the table already exists and an attempt is made
			//to create it, an exception will be thrown.
			try{
				stmt.executeUpdate("DROP TABLE " + tableName);
			}catch(Exception e){
				//System.out.print(e);
				System.out.println("No existing table to delete");
			}//end catch
		
			//Use the input parameters fields and dataTypes to create a sql statement
			String sql = "CREATE TABLE " + tableName + "(ID integer not null, ";
			if( fields.length == dataTypes.length){
				for(int i=0; i<fields.length; i++){
					sql = sql + fields[i] + " " + dataTypes[i] + ", ";
				}
			}
			
			//Make sure that ID is used as the primary key
			sql = sql + "PRIMARY KEY (ID))";
			
			//Create a table in the database named tableName.
			stmt.executeUpdate(sql);
			
			//Close the connection
			con.close();
		}catch( Exception e ) {
			e.printStackTrace();
		}//end catch
	}//end createTable
	
	/**********************************************************************
	 * A method to create the required tables for the project.
	 * Required tables include: [weightlifting, endurance, exercises]
	 **********************************************************************/
	private static void createWorkoutTables(){
		//Create weightlifting table
		String wFields[] = {"Exercise", "`Weight Type`", "`Order`", "Weight", "Reps", "`Date`", "Notes", "Location"};
		String wDataTypes[] = {"varchar(45)", "varchar(20)", "integer", "double", "integer", "date", "varchar(110)", "varchar(20)"};
		createTable("weightlifting", wFields, wDataTypes);
		
		//Create endurance table
		String eFields[] = {"Exercise", "Distance_Miles", "`Time`", "`Date`", "Notes"};
		String eDataTypes[] = {"varchar(45)", "double", "time", "date", "varchar(110)"};
		createTable("endurance", eFields, eDataTypes);
				
		//Create exercises table
		String exFields[] = {"Exercise", "Type", "`Muscle Group`", "Example"};
		String exDataTypes[] = {"varchar(45)", "varchar(20)", "varchar(20)", "varchar(200)"};
		createTable("exercises", exFields, exDataTypes);
		
	}
	
	/**********************************************************************
	 * A method to populate the exercise table in our database.  The 
	 * exercises are taken from my personal experience.
	 **********************************************************************/
	private static void populateExercises(){
		Statement stmt;
	    
		System.out.println("Populating database");
		try{
		    //Register the JDBC driver for MySQL.
		    Class.forName(driver);
		     
		    //Define URL of database server on the localhost with default port number 3306.
			String currentUrl = url + database;
			
			//Get a connection to the database 
			Connection con = DriverManager.getConnection(currentUrl, userName, password);
						
			//Get a Statement object
			stmt = con.createStatement();
			
			//Insert some starter values into the table
			int count = 0;
			
			stmt.executeUpdate( "INSERT INTO exercises(ID, Exercise, Type, `Muscle Group`, Example)" + 
			" VALUES(" + count + ", 'Skullcrushers', 'weightlifting', 'Triceps', 'http://www.myfit.ca/exercisedatabase/viewanexercise.asp?exercise=Lying+Triceps+Extension+%28Skull+Crushers%29&table=exercises&ID=57')");
			count++;
			
			stmt.executeUpdate( "INSERT INTO exercises(ID, Exercise, Type, `Muscle Group`, Example)" + 
			" VALUES(" + count + ", 'Hammer Curls', 'weightlifting', 'Biceps', 'http://www.myfit.ca/exercisedatabase/viewanexercise.asp?exercise=Hammer+Curls&table=exercises&ID=56')");
			count++;
			
			stmt.executeUpdate( "INSERT INTO exercises(ID, Exercise, Type, `Muscle Group`, Example)" + 
			" VALUES(" + count + ", 'Flat Bench Press', 'weightlifting', 'Chest', 'http://www.myfit.ca/exercisedatabase/viewanexercise.asp?exercise=Bench+Press&table=exercises&ID=1')");
			count++;
			
			stmt.executeUpdate( "INSERT INTO exercises(ID, Exercise, Type, `Muscle Group`, Example)" + 
			" VALUES(" + count + ", 'Jogging', 'endurance', " + null + ", 'http://www.fitnessproposals.com/fitness-accessories-articles/jogging-tips.htm')");
			count++;
			
			//Close the connection
			con.close();
		}
		catch (Exception e) {
			e.printStackTrace();
		}
	}
}